Subqueries and Query

Expressions

The SQL subquery feature lets you use the results of one query as part of another query.

The ability to use a query within a query was the original reason for the word

“structured” in the name Structured Query Language. The subquery feature is less

well-known than SQL’s join feature, but it plays an important role in SQL for three reasons:

• A SQL statement with a subquery is often the most natural way to express a query,

because it most closely parallels the English-language description of the query.

• Subqueries make it easier to write SELECT statements, because they let you break

a query down into pieces (the query and its subqueries) and then put the pieces

back together.

• Some queries cannot be expressed in SQL without using a subquery.

The first several sections of this chapter describe subqueries and show how they are

used in the WHERE and HAVING clauses of a SQL statement. The later sections of this chapter

describe the advanced query expression capabilities that have been added to the SQL

standard, which substantially expands the power of SQL to perform even the most complex

of database operations.

Using Subqueries

A subquery is a query within a query. The results of the subquery are used by the DBMS to

determine the results of the higher-level query that contains the subquery. In the simplest

forms of a subquery, the subquery appears within the WHERE or HAVING clause of another

SQL statement. Subqueries provide an efficient, natural way to handle query requests that

are themselves expressed in terms of the results of other queries. Here is an example of such

a request:

List the offices where the sales target for the office exceeds the sum of the salespeople’s quotas.